const connection = require('../app/database')

class MomentService {
    async create(userId, content, title) {
        const statement = `INSERT INTO posts (content, user_id, title) VALUES(?,?,?);`
        const [result] = await connection.execute(statement, [content, userId, title])
        return result
    }

    async getMomentById(userId, id) {
      console.log('shi这里')
        const statement = `
        SELECT 
        m.id id, m.content content, m.title title, m.created_at createTime, m.updated_at updateTime,
        JSON_OBJECT('id', u.id, 'name', u.username, 'avatarUrl', u.avatar) author,
        IF(COUNT(l.tag_id),JSON_ARRAYAGG(
          JSON_OBJECT('id', l.tag_id, 'name', l.tag_name)
        ),NULL) tags,
        (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
          JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.comment_time,
                      'user', JSON_OBJECT('id', cu.id, 'name', cu.username, 'avatarUrl', cu.avatar))
        ),NULL) FROM comments c LEFT JOIN users cu ON c.user_id = cu.id WHERE m.id = c.post_id) comments,
        (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.post_id) images,
        (SELECT COUNT(*) FROM article_like al WHERE al.user_id = ? AND al.article_id = m.id) isLike,
        (SELECT COUNT(*) FROM follow f WHERE f.followerId = 19 AND f.followedId = m.user_id) isFocus,
        (SELECT COUNT(*) FROM follow WHERE followedId = m.user_id) fansCount,
        (SELECT COUNT(*) FROM comments c WHERE c.post_id = m.id) commentCount,
        (SELECT COUNT(*) FROM article_like al WHERE al.article_id = m.id) likeCount
      FROM posts m
      LEFT JOIN users u ON m.user_id = u.id
      LEFT JOIN post_tags ml ON m.id = ml.post_id
      LEFT JOIN tags l ON ml.tag_id = l.tag_id
      WHERE m.id = ?
      GROUP BY m.id; `
      try {
        const [result] = await connection.execute(statement, [userId, id]);
        return result[0];
      } catch (error) {
        console.log(error)
      }
    }

    // async getMomentList(offset, size) {
    //     const statement = `
    //     SELECT 
    //     m.id id, m.content content, m.created_at createTime, m.updated_at updateTime,
    //     JSON_OBJECT('id', u.id, 'name', u.username, 'avatarUrl', u.avatar) author,
    //     IF(COUNT(l.tag_id),JSON_ARRAYAGG(
    //       JSON_OBJECT('id', l.tag_id, 'name', l.tag_name)
    //     ),NULL) tags,
    //     (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
    //       JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.comment_time,
    //                   'user', JSON_OBJECT('id', cu.id, 'name', cu.username, 'avatarUrl', cu.avatar))
    //     ),NULL) FROM comments c LEFT JOIN users cu ON c.user_id = cu.id WHERE m.id = c.post_id) comments,
    //     (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
    //     FROM file WHERE m.id = file.post_id) images
    //   FROM posts m
    //   LEFT JOIN users u ON m.user_id = u.id
    //   LEFT JOIN post_tags ml ON m.id = ml.post_id
    //   LEFT JOIN tags l ON ml.tag_id = l.tag_id
    //   WHERE m.id = ?
    //   GROUP BY m.id;  
    // `;
    // try {
    //   const [result] = await connection.execute(statement, [id]);
    //   return result[0];
    // } catch (error) {
    //   console.log(error)
    // }
    // }

    async getMomentList(userId ,offset, size) {
        try {
            const statement = `
            SELECT 
            m.id id, m.content content, m.created_at createTime, m.updated_at updateTime,
            JSON_OBJECT('id', u.id, 'name', u.username,'avatar', u.avatar) author,
            (SELECT COUNT(*) FROM comments c WHERE c.post_id = m.id) commentCount,
            (SELECT COUNT(*) FROM post_tags ml WHERE ml.post_id = m.id) tagCount,
            (SELECT COUNT(*) FROM article_like al WHERE al.article_id = m.id) likeCount,
            (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
            FROM file WHERE m.id = file.post_id) images,
            (SELECT COUNT(*) FROM article_like al WHERE al.user_id = ? AND al.article_id = m.id) isLike
          FROM posts m
          LEFT JOIN users u ON m.user_id = u.id
          ORDER BY m.created_at DESC
          LIMIT ?, ?;
            `;
        
            const [result] = await connection.execute(statement, [userId, offset, size]);
            return result;
            
        } catch (error) {
            console.log('error',error)
        }
  
      }

    async getHotMomentList(userId ,offset, size) {
        const statement = `
        SELECT posts.id, posts.title, posts.content, posts.user_id, posts.read_num,
        JSON_OBJECT('id', u.id, 'name', u.username, 'avatar', u.avatar) author,
         IFNULL(likes.like_count, 0) AS like_count, IFNULL(comments.comment_count, 0) AS comment_count,
         (posts.read_num * 0.4 + IFNULL(likes.like_count, 0) * 0.3 + IFNULL(comments.comment_count, 0) * 0.3) AS hotness_score,
           (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) FROM file WHERE posts.id = file.post_id) AS image,
           (SELECT COUNT(*) FROM article_like al WHERE al.user_id = ? AND al.article_id = posts.id) AS isLike,
         posts.created_at, posts.updated_at
       FROM posts
       LEFT JOIN (
         SELECT article_id, COUNT(*) AS like_count
         FROM article_like
         GROUP BY article_id
       ) likes ON posts.id = likes.article_id
       LEFT JOIN (
         SELECT post_id, COUNT(*) AS comment_count
         FROM comments
         GROUP BY post_id
       ) comments ON posts.id = comments.post_id
       LEFT JOIN users u ON posts.user_id = u.id
       ORDER BY hotness_score DESC
       LIMIT ?, ?;
        `;

        const [result] = await connection.execute(statement, [userId, offset, size]);
        return result;
    }

    async update(momentId, content) {
        try {
            const statement = `UPDATE moment SET content = ? WHERE id =?;`
            const result = await connection.execute(statement, [content, momentId])
            return result
        } catch (error) {
            console.log(error)
        }
    }
    
    async  remove(momentId) {
        try {
            const statement = `DELETE FROM moment WHERE id = ?;`
            const [result] = await connection.execute(statement, [momentId])
            return result
        } catch (error) {
            console.log(error)
        }

    }
    
    async hasLabel(momentId, tagId) {
        const statement = `SELECT * FROM post_tags WHERE post_id = ? AND tag_id = ?`;
        try {
            const [result] = await connection.execute(statement, [momentId, tagId]);
            return result[0] ? true: false;
        } catch (error) {
        }
        
    }

    async addLabel(momentId, tagId) {
        try {
            const statement = `INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?);`;
            const [result] = await connection.execute(statement, [momentId, tagId]);
            return result;

        } catch (error) {
            console.log('error', error)
        }
       
    }

    async getUserMomentList(userId, offset, size) {
        const statement = `   SELECT posts.id, posts.title, posts.content, posts.user_id, posts.read_num,
        JSON_OBJECT('id', u.id, 'name', u.username, 'avatar', u.avatar) author,
        IFNULL(likes.like_count, 0) AS like_count, IFNULL(comments.comment_count, 0) AS comment_count,
        (posts.read_num * 0.4 + IFNULL(likes.like_count, 0) * 0.3 + IFNULL(comments.comment_count, 0) * 0.3) AS hotness_score,
          (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) FROM file WHERE posts.id = file.post_id) AS image,
          (SELECT COUNT(*) FROM article_like al WHERE al.user_id = ? AND al.article_id = posts.id) isLike,
        posts.created_at, posts.updated_at
      FROM posts
      LEFT JOIN (
        SELECT article_id, COUNT(*) AS like_count
        FROM article_like
        GROUP BY article_id
      ) likes ON posts.id = likes.article_id
      LEFT JOIN (
        SELECT post_id, COUNT(*) AS comment_count
        FROM comments
        GROUP BY post_id
      ) comments ON posts.id = comments.post_id
      LEFT JOIN users u ON posts.user_id = u.id
      WHERE u.id = ?
      ORDER BY hotness_score DESC
      LIMIT ?, ?;`;
      const [result] = await connection.execute(statement, [userId, userId, offset, size]);
      return result;
    }

    async getUserMomentListById(userId) {
      console.log('d')
      const statement = `SELECT p.content,p.id, p.title,  (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) FROM file WHERE p.id = file.post_id) AS image
      FROM posts  p
      LEFT JOIN users u
      ON u.id = p.user_id
      WHERE user_id = ?;`;
      const [result] =  await connection.execute(statement, [userId])
      return result
    }
}

module.exports = new MomentService()
